# -*- coding: utf-8 -*-
# Create time: 2020-03-10 13:21
# Author:Rongzhikai
import openpyxl
from conf.settings import DATA_FILE, SHEETNAME


class Datafromexcel:
    """读取测试数据Excel表，整理数据，打包成字典"""

    def __init__(self, data_path=DATA_FILE, sheet_name=SHEETNAME):
        self.data_file = data_path
        self.workbook = openpyxl.load_workbook(self.data_file)
        self.data_sheet = self.workbook[sheet_name]
        self.cases_id = self.__get_col_values(3)  # 设定唯一标识用例的列数据
        self.col_result = self.__col_for_result()

    # 获取测试数据字典
    def getdata(self):
        # 获取第二行的内容作为请求必需的key
        keys = self.__get_row_values(2)

        data_rows = self.data_sheet.max_row
        # 组合所有测试数据为列表
        data_dicts = []
        for i in range(3, data_rows + 1):
            values = self.__get_row_values(i)
            dicts_row = dict(zip(keys, values))
            data_dicts.append(dicts_row)
        return data_dicts

    # 获取某一行数据
    def __get_row_values(self, row):
        data_cols = self.data_sheet.max_column
        values = []
        for i in range(1, data_cols + 1):
            value = self.__get_value(row, i)
            values.append(value)
        return values

    # 获取某一列的数据
    def __get_col_values(self, col):
        data_rows = self.data_sheet.max_row
        values = []
        for i in range(1, data_rows + 1):
            value = self.__get_value(i, col)
            values.append(value)
        return values

    # 获取单元格数据
    def __get_value(self, row, col):
        return self.data_sheet.cell(row, col).value

    # 获取数据所在的行数
    def get_line_num(self, value):
        row = 0
        for i in self.cases_id:
            row += 1
            if i == value:
                break
        return row

    # 写入单元格数据
    def write_result(self, row, result):
        col = self.col_result
        self.data_sheet.cell(row, col).value = result
        self.workbook.save(filename=self.data_file)

    # 查找结果result所在的列数
    def __col_for_result(self):
        col = 0
        for i in self.__get_row_values(2):
            col += 1
            if i == 'result':
                break
        return col


if __name__ == '__main__':
    print(Datafromexcel().getdata())
